package ai.xiaodao.db;

import ai.xiaodao.config.DBConfig;
import ai.xiaodao.tool.ConfigUtil;
import org.apache.log4j.Logger;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBStore {

    private static String dbName = DBConfig.getDBName();
    //    private static String basedir = System.getProperty("user.dir");
    private static String dataDir = DBConfig.getDataDir();

    static Logger log = Logger.getLogger(DBStore.class.getName());

    public DBStore() {
        try {
            init();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static class DBStoreHolder {
        static DBStore instance = new DBStore();
    }

    public static DBStore getInstance() {
        return DBStoreHolder.instance;
    }

    public synchronized void init() {
        File dbDir = new File(dataDir);
        if (!dbDir.exists()) {
            dbDir.mkdirs();
        }
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        File dbFile = new File(dbName);
        if (!dbFile.exists()) {

        }
    }

    public Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:sqlite:" + dbName);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }

    public int exesql(String sql, Connection connection) {
        Statement statement;
        int result = 0;
        try {
            statement = connection.createStatement();
            statement.setQueryTimeout(30);
            result = statement.executeUpdate(sql);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return result;
    }

    public void createTable() {
        Connection conn = getConnection();
        String sql = "create table sys_user(id integer primary key autoincrement ,username string ,password string)";
        exesql(sql, conn);
        close(conn);
    }

    public ResultSet query(String sql, Connection connection) {
        Statement statement;
        ResultSet result = null;
        try {
            statement = connection.createStatement();
            statement.setQueryTimeout(30);
            result = statement.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return result;
    }

    public int deletee(String sql, Connection connection) {
        Statement statement;
        int result = 0;
        try {
            statement = connection.createStatement();
            statement.setQueryTimeout(30);
            result = statement.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return result;
    }

    public void close(Connection conn) {
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * get the query results from a database query
     *
     * @param resultSet q query results
     * @return rows of the query results
     */

    public static int getRows(ResultSet resultSet) {
        int rowCount = 0;
        log.debug("querying result rows");
        try {
            while (resultSet.next()) {
                rowCount++;
            }
        } catch (SQLException e) {
            log.error("query database error: " + e.getMessage());
        }
        log.debug("the  result row number is :" + rowCount);
        return rowCount;
    }
}
